package com.softserveinc.data.help;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MovieDBOpenHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "MovieManagerDB";
	private static final int DATABASE_VERSION = 2;

	public MovieDBOpenHelper(final Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(final SQLiteDatabase db) {
		createMoviesTable(db);
		createMovieDetailsTable(db);
		createMovieDetailsImageTable(db);
		createDataListsTable(db);
		createMoviesDataListsTable(db);
		createMoviesSearchTable(db);
	}

	@Override
	public void onUpgrade(final SQLiteDatabase db, final int oldVersion,
			final int newVersion) {

		db.execSQL("DROP TABLE IF EXISTS " + MoviesTableMeta.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + MovieDetailsTableMeta.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + MovieImagesTableMeta.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + DataListsTableMeta.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + MoviesSearchTableMeta.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS "
				+ MoviesDataListsTableMeta.TABLE_NAME);

		onCreate(db);
	}

	private void createMoviesTable(final SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + MoviesTableMeta.TABLE_NAME + " ("
				+ MoviesTableMeta.COLUMN_NAME_TMDB_ID + " INTEGER PRIMARY KEY,"
				+ MoviesTableMeta.COLUMN_NAME_TITLE + " TEXT,"
				+ MoviesTableMeta.COLUMN_NAME_POPULARITY + " REAL,"
				+ MoviesTableMeta.COLUMN_NAME_POSTER_URL + " TEXT,"
				+ MoviesTableMeta.COLUMN_NAME_POSTER_LOCAL_URL + " TEXT,"
				+ MoviesTableMeta.COLUMN_NAME_CERTIFICATION_NAME + " TEXT,"
				+ MoviesTableMeta.COLUMN_NAME_RELEASE_DATE + " TEXT);");
	}

	private void createMovieDetailsTable(final SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + MovieDetailsTableMeta.TABLE_NAME + " ("
				+ MovieDetailsTableMeta.COLUMN_NAME_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ MovieDetailsTableMeta.COLUMN_NAME_OVERVIEW + " TEXT, "
				+ MovieDetailsTableMeta.COLUMN_NAME_CAST + " TEXT, "
				+ MovieDetailsTableMeta.COLUMN_NAME_MOVIE_TRAILER_URL
				+ " TEXT," + MovieDetailsTableMeta.COLUMN_NAME_MOVIE_TMBD_ID
				+ " INTEGER, FOREIGN KEY("
				+ MovieDetailsTableMeta.COLUMN_NAME_MOVIE_TMBD_ID
				+ ") REFERENCES " + MoviesTableMeta.TABLE_NAME + "("
				+ MoviesTableMeta.COLUMN_NAME_TMDB_ID + "));");
	}

	private void createMovieDetailsImageTable(final SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + MovieImagesTableMeta.TABLE_NAME + " ("
				+ MovieImagesTableMeta.COLUMN_NAME_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ MovieImagesTableMeta.COLUMN_NAME_THUMB_LOCAL + " TEXT,"
				+ MovieImagesTableMeta.COLUMN_NAME_THUMB_HTTP + " TEXT,"
				+ MovieImagesTableMeta.COLUMN_NAME_THUMB_HTTP_BIG + " TEXT,"
				+ MovieImagesTableMeta.COLUMN_NAME_MOVIE_TMDB_ID
				+ " INTEGER, FOREIGN KEY("
				+ MovieImagesTableMeta.COLUMN_NAME_MOVIE_TMDB_ID
				+ ") REFERENCES " + MoviesTableMeta.TABLE_NAME + "("
				+ MoviesTableMeta.COLUMN_NAME_TMDB_ID + "));");
	}

	private void createDataListsTable(final SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + DataListsTableMeta.TABLE_NAME + " ("
				+ DataListsTableMeta.COLUMN_NAME_LIST_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ DataListsTableMeta.COLUMN_NAME_LIST_TYPE + " STRING,"
				+ DataListsTableMeta.COLUMN_NAME_LAST_MODIFIED + " INTEGER);");
	}

	private void createMoviesDataListsTable(final SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + MoviesDataListsTableMeta.TABLE_NAME + " ("
				+ MoviesDataListsTableMeta.COLUMN_NAME_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ MoviesDataListsTableMeta.COLUMN_NAME_MOVIE_TMDB_ID_REF
				+ " INTEGER,"
				+ MoviesDataListsTableMeta.COLUMN_NAME_LIST_ID_REF
				+ " INTEGER, FOREIGN KEY("
				+ MoviesDataListsTableMeta.COLUMN_NAME_MOVIE_TMDB_ID_REF
				+ ") REFERENCES " + MoviesTableMeta.TABLE_NAME + "("
				+ MoviesTableMeta.COLUMN_NAME_TMDB_ID + "), FOREIGN KEY("
				+ MoviesDataListsTableMeta.COLUMN_NAME_LIST_ID_REF
				+ ") REFERENCES " + DataListsTableMeta.TABLE_NAME + "("
				+ DataListsTableMeta.COLUMN_NAME_LIST_ID
				+ ") ON DELETE CASCADE);");
	}

	private void createMoviesSearchTable(final SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + MoviesSearchTableMeta.TABLE_NAME + " ("
				+ MoviesSearchTableMeta.COLUMN_NAME_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ MoviesSearchTableMeta.COLUMN_NAME_SEARCH_KEY + " TEXT);");
	}

	public static class MoviesTableMeta {
		private MoviesTableMeta() {
		}

		public static final String TABLE_NAME = "Movies";

		public static final String COLUMN_NAME_TMDB_ID = "TMDB_ID";
		public static final String COLUMN_NAME_TITLE = "Title";
		public static final String COLUMN_NAME_POPULARITY = "Popularity";
		public static final String COLUMN_NAME_POSTER_URL = "PosterUrl";
		public static final String COLUMN_NAME_POSTER_LOCAL_URL = "PosterLocalUrl";
		public static final String COLUMN_NAME_RELEASE_DATE = "ReleaseDate";
		public static final String COLUMN_NAME_CERTIFICATION_NAME = "Certification";

		public static String[] COLUMN_NAMES = new String[] {
				COLUMN_NAME_TMDB_ID, COLUMN_NAME_TITLE, COLUMN_NAME_POPULARITY,
				COLUMN_NAME_POSTER_URL, COLUMN_NAME_POSTER_LOCAL_URL,
				COLUMN_NAME_RELEASE_DATE, COLUMN_NAME_CERTIFICATION_NAME };
	}

	public static class MovieDetailsTableMeta {
		private MovieDetailsTableMeta() {
		}

		public static final String TABLE_NAME = "MovieDetails";

		public static final String COLUMN_NAME_ID = "ID";
		public static final String COLUMN_NAME_OVERVIEW = "Overview";
		public static final String COLUMN_NAME_CAST = "ActorsCast";
		public static final String COLUMN_NAME_MOVIE_TMBD_ID = "MovieTmdbIDRef";
		public static final String COLUMN_NAME_MOVIE_TRAILER_URL = "MovieTrailerUrl";
	}

	public static class MovieImagesTableMeta {
		private MovieImagesTableMeta() {
		}

		public static final String TABLE_NAME = "MovieImages";

		public static final String COLUMN_NAME_ID = "ID";
		public static final String COLUMN_NAME_THUMB_HTTP = "ThumbHttp";
		public static final String COLUMN_NAME_THUMB_HTTP_BIG = "ThumbHttpBig";
		public static final String COLUMN_NAME_THUMB_LOCAL = "ThumbLocal";
		public static final String COLUMN_NAME_MOVIE_TMDB_ID = "MovieIDRef";
	}

	public static class DataListsTableMeta {
		private DataListsTableMeta() {
		}

		public static final String TABLE_NAME = "DataLists";

		public static final String COLUMN_NAME_LIST_ID = "ID";
		public static final String COLUMN_NAME_LIST_TYPE = "ListType";
		public static final String COLUMN_NAME_LAST_MODIFIED = "LastModified";
	}

	public static class MoviesDataListsTableMeta {
		private MoviesDataListsTableMeta() {
		}

		public static final String TABLE_NAME = "MovieDataLists";

		public static final String COLUMN_NAME_ID = "ID";
		public static final String COLUMN_NAME_MOVIE_TMDB_ID_REF = "MovieIDRef";
		public static final String COLUMN_NAME_LIST_ID_REF = "OwnerIDRef";
	}

	public static class MoviesSearchTableMeta {
		private MoviesSearchTableMeta() {
		}

		public static final String TABLE_NAME = "MovieSearch";

		public static final String COLUMN_NAME_ID = "ID";
		public static final String COLUMN_NAME_SEARCH_KEY = "SearchValue";
	}
}
